﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class FormChinhDAO
    {
        public DataTable Loadhoadon()
        {
            Data a = new Data();
            DataTable d = new DataTable();
            string sql = "select * from HoaDon";
            d = a.ExecuteQuery(sql);
            return d;
        }
        public DataTable Loadhoadonngay(FormChinhDTO DTO)
        {
            Data a = new Data();
            DataTable d = new DataTable();
            string sql = "select * from HoaDon where Ngay='"+DTO.Ngay + "'";
            d = a.ExecuteQuery(sql);
            return d;
        }
        public DataTable Loadchitiethoadon( FormChinhDTO DTO )
        {
            Data a = new Data();
            DataTable d = new DataTable();
            string sql = "select * from HoaDon H  where  H.MaHoaDon = " + DTO.MaHoaDon.ToString();
            d = a.ExecuteQuery(sql);
            return d;
        }
        public DataTable Loadchitiethoadonmonan(FormChinhDTO DTO)
        {
            Data a = new Data();
            DataTable d = new DataTable();
            string sql = "select TenMon,SL,C.ThanhTien,MaChiTietHoaDon  from HoaDon H,ChiTietHoaDon C,bangmonan b  where C.MaHoaDon=H.MaHoaDon and C.MaMon=b.MaMon and H.MaHoaDon = " + DTO.MaHoaDon.ToString();
            d = a.ExecuteQuery(sql);
            return d;
        }
        public DataTable Loadbanan()
        {
            Data a = new Data();
            DataTable d = new DataTable();
            string sql = "select * from BanAn where IsDelete = '0'";
            d = a.ExecuteQuery(sql);
            return d;
        }
        public DataTable LoadNV()
        {
            Data a = new Data();
            DataTable d = new DataTable();
            string sql = "select * from NhanVien";
            d = a.ExecuteQuery(sql);
            return d;
        }
        public DataTable LoadVAT()
        {
            Data a = new Data();
            DataTable d = new DataTable();
            string sql = "select * from ThamSo";
            d = a.ExecuteQuery(sql);
            return d;
        }
        public DataTable Loadtenmon()
        {
            Data a = new Data();
            DataTable d = new DataTable();
            string sql = "select * from bangmonan";
            d = a.ExecuteQuery(sql);
            return d;
        }
        public void xoahoadon(FormChinhDTO DTO)
        {
            Data a = new Data();
            DataTable d = new DataTable();
            string sql = "delete from ChiTietHoaDon where MaHoaDon = "+ DTO.MaHoaDon;
            string sql2 = "delete from HoaDon where MaHoaDon= " + DTO.MaHoaDon;
             a.ExecuteQuery(sql);
             a.ExecuteQuery(sql2); 
        }
        public void Themhoadon(FormChinhDTO DTO)
        {
            Data a = new Data();
            string sql = "insert into HoaDon values(N'"+ DTO.TenKhach + "','" + DTO.Ngay + "','" + DTO.MaNV + "','" + DTO.MaBan + "','" + DTO.Tongtien + "','" + DTO.Buoi +"')";
            a.ExecuteQuery(sql);
        }
        public void Themchitiethoadon(FormChinhDTO DTO)
        {
            Data a = new Data();
            string sql = "insert into ChiTietHoaDon values('" + DTO.MaHoaDon + "','" + DTO.MaMon + "','" + DTO.SoLuong + "','" + DTO.ThanhTien +"')";
            a.ExecuteNonQuery(sql);
        }
        public DataTable xuatmamonan(FormChinhDTO DTO)
        {
            Data a = new Data();
            DataTable d = new DataTable();
            string sql = "select MaMon from bangmonan where TenMon=N'" + DTO.Ngay +"'";
            d = a.ExecuteQuery(sql);
            return d;
        }
        public void suahoadon(FormChinhDTO DTO)
        {
            Data a = new Data();
            string sql = "update HoaDon set TenKhach= N'" + DTO.TenKhach + "',Ngay='" + DTO.Ngay + "',MaNV='" + DTO.MaNV + "',MaBan='" + DTO.MaBan + "',TongTien='" + DTO.Tongtien + "',Buoi='" + DTO.Buoi + "' where MaHoaDon='" + DTO.MaHoaDon + "'";
            a.ExecuteQuery(sql);
        }
        public void suachitiethoadon(FormChinhDTO DTO)
        {
            Data a = new Data();
            string sql = "update ChiTietHoaDon set MaMon='" + DTO.MaMon + "',SL='" + DTO.SoLuong + "',ThanhTien='" + DTO.ThanhTien + "' where MaChiTietHoaDon= '" + DTO.MaChiTietHoaDon+ "'";
            a.ExecuteQuery(sql);
        }
        public void xoachitiet(FormChinhDTO DTO)
        {
            Data a = new Data();
            string sql = "delete from ChiTietHoaDon where MaChiTietHoaDon='" + DTO.MaChiTietHoaDon + "'";
            a.ExecuteQuery(sql);
        }
        public void xoachitiethet(FormChinhDTO DTO)
        {
            Data a = new Data();
            string sql = "delete from ChiTietHoaDon where MaHoaDon ='" + DTO.MaHoaDon + "'";
            a.ExecuteQuery(sql);
        }
        public DataTable max()
        {
            Data a = new Data();
            string sql = "select max(MaHoaDon) from HoaDon";
            return a.ExecuteQuery(sql);
        }
    }
}
